using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using iBeauty.BusinessObjects;

namespace iBeauty.DAL
{
	public class ServiceDAO
	{
		public ServiceDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<Service> GetServices()
		{
			try
			{
				List<Service> lstServices = new List<Service>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[ServiceCode],[ServiceName],[Price],[ForeignPrice],[Currency],[Barcode],[AccountId],[Note] FROM [Service]",CommandType.Text);
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					Service oService = new Service();
					oService.ID = Convert.ToString(oDbDataReader["ID"]);

					if(oDbDataReader["CreateDate"] != DBNull.Value)
						oService.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

					if(oDbDataReader["ModifiedDate"] != DBNull.Value)
						oService.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

					if(oDbDataReader["TimeStamp"] != DBNull.Value)
						oService.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

					if(oDbDataReader["ServiceCode"] != DBNull.Value)
						oService.ServiceCode = Convert.ToString(oDbDataReader["ServiceCode"]);

					if(oDbDataReader["ServiceName"] != DBNull.Value)
						oService.ServiceName = Convert.ToString(oDbDataReader["ServiceName"]);

					if(oDbDataReader["Price"] != DBNull.Value)
						oService.Price = Convert.ToSingle(oDbDataReader["Price"]);

					if(oDbDataReader["ForeignPrice"] != DBNull.Value)
						oService.ForeignPrice = Convert.ToSingle(oDbDataReader["ForeignPrice"]);

					if(oDbDataReader["Currency"] != DBNull.Value)
						oService.Currency = Convert.ToString(oDbDataReader["Currency"]);

					if(oDbDataReader["Barcode"] != DBNull.Value)
						oService.Barcode = Convert.ToString(oDbDataReader["Barcode"]);

					if(oDbDataReader["AccountId"] != DBNull.Value)
						oService.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

					if(oDbDataReader["Note"] != DBNull.Value)
						oService.Note = Convert.ToString(oDbDataReader["Note"]);
					lstServices.Add(oService);
				}
				oDbDataReader.Close();
				return lstServices;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public Service GetService(string ID)
		{
			try
			{
				Service oService = new Service();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[ServiceCode],[ServiceName],[Price],[ForeignPrice],[Currency],[Barcode],[AccountId],[Note] FROM [Service] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oService.ID = Convert.ToString(oDbDataReader["ID"]);

					if(oDbDataReader["CreateDate"] != DBNull.Value)
						oService.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

					if(oDbDataReader["ModifiedDate"] != DBNull.Value)
						oService.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

					if(oDbDataReader["TimeStamp"] != DBNull.Value)
						oService.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

					if(oDbDataReader["ServiceCode"] != DBNull.Value)
						oService.ServiceCode = Convert.ToString(oDbDataReader["ServiceCode"]);

					if(oDbDataReader["ServiceName"] != DBNull.Value)
						oService.ServiceName = Convert.ToString(oDbDataReader["ServiceName"]);

					if(oDbDataReader["Price"] != DBNull.Value)
						oService.Price = Convert.ToSingle(oDbDataReader["Price"]);

					if(oDbDataReader["ForeignPrice"] != DBNull.Value)
						oService.ForeignPrice = Convert.ToSingle(oDbDataReader["ForeignPrice"]);

					if(oDbDataReader["Currency"] != DBNull.Value)
						oService.Currency = Convert.ToString(oDbDataReader["Currency"]);

					if(oDbDataReader["Barcode"] != DBNull.Value)
						oService.Barcode = Convert.ToString(oDbDataReader["Barcode"]);

					if(oDbDataReader["AccountId"] != DBNull.Value)
						oService.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

					if(oDbDataReader["Note"] != DBNull.Value)
						oService.Note = Convert.ToString(oDbDataReader["Note"]);
				}
				oDbDataReader.Close();
				return oService;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddService(string ID,DateTime?CreateDate,DateTime?ModifiedDate,DateTime?TimeStamp,string ServiceCode,string ServiceName,Single?Price,Single?ForeignPrice,string Currency,string Barcode,int?AccountId,string Note)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Service]([CreateDate],[ModifiedDate],[TimeStamp],[ServiceCode],[ServiceName],[Price],[ForeignPrice],[Currency],[Barcode],[AccountId],[Note])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@ServiceCode,@ServiceName,@Price,@ForeignPrice,@Currency,@Barcode,@AccountId,@Note)",CommandType.Text);
				if (CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (ServiceCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,ServiceCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,DBNull.Value));
				if (ServiceName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,ServiceName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,DBNull.Value));
				if (Price.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,Price));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,DBNull.Value));
				if (ForeignPrice.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,ForeignPrice));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,DBNull.Value));
				if (Currency!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,Currency));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,DBNull.Value));
				if (Barcode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,Barcode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,DBNull.Value));
				if (AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddService(Service oService)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Service]([CreateDate],[ModifiedDate],[TimeStamp],[ServiceCode],[ServiceName],[Price],[ForeignPrice],[Currency],[Barcode],[AccountId],[Note])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@ServiceCode,@ServiceName,@Price,@ForeignPrice,@Currency,@Barcode,@AccountId,@Note)",CommandType.Text);
				if (oService.CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,oService.CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (oService.ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,oService.ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (oService.TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,oService.TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (oService.ServiceCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,oService.ServiceCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,DBNull.Value));
				if (oService.ServiceName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,oService.ServiceName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,DBNull.Value));
				if (oService.Price.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,oService.Price));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,DBNull.Value));
				if (oService.ForeignPrice.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,oService.ForeignPrice));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,DBNull.Value));
				if (oService.Currency!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,oService.Currency));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,DBNull.Value));
				if (oService.Barcode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,oService.Barcode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,DBNull.Value));
				if (oService.AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,oService.AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (oService.Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,oService.Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateService(string ID,DateTime?CreateDate,DateTime?ModifiedDate,DateTime?TimeStamp,string ServiceCode,string ServiceName,Single?Price,Single?ForeignPrice,string Currency,string Barcode,int?AccountId,string Note)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Service] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[ServiceCode]=@ServiceCode,[ServiceName]=@ServiceName,[Price]=@Price,[ForeignPrice]=@ForeignPrice,[Currency]=@Currency,[Barcode]=@Barcode,[AccountId]=@AccountId,[Note]=@Note WHERE [ID]=@ID",CommandType.Text);
				if (CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (ServiceCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,ServiceCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,DBNull.Value));
				if (ServiceName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,ServiceName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,DBNull.Value));
				if (Price.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,Price));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,DBNull.Value));
				if (ForeignPrice.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,ForeignPrice));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,DBNull.Value));
				if (Currency!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,Currency));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,DBNull.Value));
				if (Barcode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,Barcode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,DBNull.Value));
				if (AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateService(Service oService)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Service] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[ServiceCode]=@ServiceCode,[ServiceName]=@ServiceName,[Price]=@Price,[ForeignPrice]=@ForeignPrice,[Currency]=@Currency,[Barcode]=@Barcode,[AccountId]=@AccountId,[Note]=@Note WHERE [ID]=@ID",CommandType.Text);
				if (oService.CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,oService.CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (oService.ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,oService.ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (oService.TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,oService.TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (oService.ServiceCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,oService.ServiceCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceCode",DbType.String,DBNull.Value));
				if (oService.ServiceName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,oService.ServiceName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ServiceName",DbType.String,DBNull.Value));
				if (oService.Price.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,oService.Price));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Price",DbType.Single,DBNull.Value));
				if (oService.ForeignPrice.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,oService.ForeignPrice));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ForeignPrice",DbType.Single,DBNull.Value));
				if (oService.Currency!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,oService.Currency));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Currency",DbType.String,DBNull.Value));
				if (oService.Barcode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,oService.Barcode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode",DbType.String,DBNull.Value));
				if (oService.AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,oService.AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (oService.Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,oService.Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,oService.ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveService(Service oService)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Service] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,oService.ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveService(string ID)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Service] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
